const connection = require('../../database/connection')

module.exports = {
    async getStats_plansOfCenter(data) {
        const [plans] = await connection.execute('SELECT ANY_VALUE ( c.`name` ) `name`, SUM( `visit_plan` ) AS `visit_plan`, SUM( `sign_plan` ) AS `sign_plan`, count(*) amount FROM `plans` p, `centers` c WHERE p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND c.`id` = p.`center_id` AND c.`name` = "' + data.center + '" AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY p.`time` having amount>=1 ORDER BY amount;')
        return plans
    },
    async getStats_plansOfDept(data) {
        const [plans] = await connection.execute('SELECT p.*, d.`name` dept_name, c.`name` center_name FROM `plans` p, `departments` d, `centers` c WHERE p.`time` >= "' + data.from + '"  AND p.`time` <= "' + data.to + '" AND ' + data.sql + ' d.`id` = p.`department_id` AND c.`id` = p.`center_id` AND p.`deleted_at` IS NULL AND d.`deleted_at` IS NULL ORDER BY p.`id`;')
        return plans
    },
    async getStats_RateOfCenter(data) {
        const [visitsRate] = await connection.execute('SELECT ANY_VALUE (r.`time`) `time`, ANY_VALUE (c.`name`) `name`, count(*) amount FROM `records` r, `centers` c WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND ' + data.sql + ' c.`id` = r.`center_id` AND c.`name` = "' + data.center + '" AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY r.`time` having amount>=1;')
        return visitsRate
    },
    async getStats_RateOfDept(data) {
        const [visitsRate] = await connection.execute('SELECT ANY_VALUE (r.`time`) `time`, d.`name` dept_name, ANY_VALUE (c.`name`) `center_name`, count( * ) amount FROM `records` r, `centers` c, `departments` d WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND ' + data.sql + ' c.`id` = r.`center_id` AND d.`id` = r.`department_id` AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY r.`department_id` HAVING amount >= 1;')
        return visitsRate
    },
    async getStats_negotiator(data) {
        const [negotiators] = await connection.execute('SELECT n.`name` negotiator_name, count( * ) amount FROM `records` r, `negotiators` n WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND ' + data.sql + ' n.`id` = r.`negotiator_id` AND r.`deleted_at` IS NULL AND n.`deleted_at` IS NULL GROUP BY r.`negotiator_id` HAVING amount >= 1;')
        return negotiators
    },
    async getStats_negotiatorList(data) {
        const [negotiators] = await connection.execute('SELECT n.`name` FROM `negotiators` n')
        return negotiators
    },
    async getStats_warrantManRank(data) {
        const [warrantManRanks] = await connection.execute('SELECT wm.`name` warrantman_name, count(*) amount FROM `records` r, `warrant_mans` wm WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '" AND ' + data.sql + ' r.`warrant_man_id` = wm.`id` AND r.`deleted_at` IS NULL AND wm.`deleted_at` IS NULL GROUP BY r.`warrant_man_id` having amount>=1 ORDER BY amount DESC LIMIT 10;')
        return warrantManRanks
    },
    async getStats_salesmanRank(data) {
        const [salesmanRanks] = await connection.execute('SELECT s.`name` salesman_name, count(*) amount FROM `records` r, `salesmans` s WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '" AND ' + data.sql + ' r.`salesman_id` = s.`id` AND r.`deleted_at` IS NULL AND s.`deleted_at` IS NULL GROUP BY r.`salesman_id` having amount>=1 ORDER BY amount DESC;')
        return salesmanRanks
    },
    async getStats_salesmanRankCharge(data) {
        const [charges] = await connection.execute('SELECT s.`name` salesman_name, sum(r.`charge`) sum, count(*) amount FROM `records` r, `salesmans` s WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND ' + data.sql + ' r.`salesman_id` = s.`id` AND r.`deleted_at` IS NULL AND s.`deleted_at` IS NULL GROUP BY r.`salesman_id` having amount>=1 ORDER BY sum DESC;')
        return charges
    },
    async getStats_recordsFilter(data) {
        const [records] = await connection.execute('SELECT ANY_VALUE (pc.`name`) `promotion_name`, ANY_VALUE (r.`center_id`) `center_id`, ANY_VALUE (r.`department_id`) `department_id`, count(*) amount FROM `records` r, `promotion_channels` pc WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '" AND ' + data.sql + ' r.`promotion_id` = pc.`id` AND r.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL GROUP BY r.`' + data.group + '` having amount>=1;')
        return records
    },
    async getStats_WarrTransform(data) {
        const [WarrTransforms] = await connection.execute('SELECT r.`pass_time`, count(*) amount FROM `records` r, `trial_channels` tc, `warrant_mans` wm WHERE r.`pass_time` >= "' + data.from + '" AND r.`pass_time` <= "' + data.to + '" AND ' + data.sql + ' r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`trial_id` = tc.`id` AND r.`warrant_man_id` = wm.`id` AND r.`deleted_at` IS NULL AND tc.`deleted_at` IS NULL AND wm.`deleted_at` IS NULL GROUP BY r.`pass_time` having amount>=1;')
        return WarrTransforms
    },
    async getStats_WarrPicks(data) {
        const [WarrTransforms] = await connection.execute('SELECT r.`pick_car_time`, count( * ) amount FROM `records` r, `trial_channels` tc, `warrant_mans` wm WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND ' + data.sql + ' r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`trial_id` = tc.`id` AND r.`warrant_man_id` = wm.`id` AND r.`deleted_at` IS NULL AND tc.`deleted_at` IS NULL AND wm.`deleted_at` IS NULL GROUP BY r.`pick_car_time` HAVING amount >= 1;')
        return WarrTransforms
    },
    async getStats_WarrOrdertaking(data) {
        const [WarrOrdertakings] = await connection.execute('SELECT r.`time`, count( * ) amount FROM `records` r, `warrant_mans` wm WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND ' + data.sql + 'r.`warrant_man_id` = wm.`id` AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`deleted_at` IS NULL GROUP BY r.`time` HAVING amount >= 1;')
        return WarrOrdertakings
    },
    async getStats_packagesFilter(data) {
        const [pakages] = await connection.execute('SELECT sum( `amount` ) sum, ANY_VALUE ( p.`amount` ) `amount`, pc.`name` promotion_name, count( * ) count FROM `packages` p, `promotion_channels` pc WHERE ' + data.sql + ' p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND p.`promotion_id` = pc.`id` AND p.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL GROUP BY p.`promotion_id` HAVING count >= 1')
        return pakages
    },
    async getStats_ChargeFilter(data) {
        const [charges] = await connection.execute('SELECT ANY_VALUE (pc.`name` ) `promotion_name`, ANY_VALUE ( r.`center_id` ) `center_id`, ANY_VALUE ( r.`department_id` ) `department_id`, sum(r.`charge`) sum, count(*) amount FROM `records` r, `promotion_channels` pc WHERE ' + data.sql + ' r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`promotion_id` = pc.`id` AND r.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL GROUP BY r.`' + data.group + '` having amount>=1;')
        return charges
    },
    async getStats_yearOperate(data) {
        const [yearOperates] = await connection.execute('SELECT MONTH ( ' + data.group + ' ) m, ' + data.sum + ' count( * ) amount FROM records r WHERE ' + data.sql + ' YEAR ( ' + data.group + ' ) = ' + data.year + ' AND r.`deleted_at` IS NULL GROUP BY ' + data.group + '')
        return yearOperates
    },
    async getStats_buyType(data) {
        const [buyTypes] = await connection.execute('SELECT ' + data.group + ', count( * ) amount FROM `records` r WHERE YEAR ( time ) = ' + data.year + ' ' + data.sql + ' AND r.`deleted_at` IS NULL GROUP BY ' + data.group + ';')
        return buyTypes
    },
    async getStats_carType(data) {
        const [carTypes] = await connection.execute('SELECT r.`cartype`, count( * ) amount FROM `records` r WHERE YEAR ( time ) = ' + data + ' AND r.`is_ordertaking` = 1 AND r.`pick_car_time` != "" AND r.`deleted_at` IS NULL GROUP BY r.`cartype`;')
        return carTypes
    },
    async getYearOperateDistr(data) {
        const [yearOperates] = await connection.execute('SELECT MONTH ( time ) m, sum( p.`amount` ) sum, count( * ) amount FROM packages p WHERE YEAR ( time ) = ' + data + ' AND p.`deleted_at` IS NULL GROUP BY time')
        return yearOperates
    },
    async getStats_CostFilter(data) {
        const [costs] = await connection.execute('SELECT pc.`name` promotion_name, sum(p.`cost`) sum, sum(p.`amount`) amount, count(*) t FROM `packages` p, `promotion_channels` pc WHERE ' + data.sql + ' p.`promotion_id` = pc.`id` AND p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND p.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL GROUP BY p.`promotion_id` having t>=1;')
        return costs
    },
    async getStats_pakages(data) {
        const [pakages] = await connection.execute('SELECT ANY_VALUE ( p.`amount` ) `amount`, ANY_VALUE ( p.`time` ) `time`, sum( p.`amount` ) total, count( * ) sum FROM `packages` p WHERE p.`time` >= "' + data.from + '"  AND p.`time` <= "' + data.to + '" AND p.`deleted_at` IS NULL GROUP BY p.`time` HAVING sum >= 1 ORDER BY p.`time`;')
        return pakages
    },
    async getStats_visits(data) {
        const [visits] = await connection.execute('SELECT r.`time`, count(*) amount FROM `records` r WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`deleted_at` IS NULL GROUP BY r.`time` having amount>=1;')
        return visits
    },
    async getStats_signs(data) {
        const [signs] = await connection.execute('SELECT r.`time`, count(*) amount FROM `records` r WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`deleted_at` IS NULL GROUP BY r.`time` having amount>=1;')
        return signs
    },
    async getStats_warrantOrdertakings(data) {
        const [warrantOrdertakings] = await connection.execute('SELECT r.`time`, count(*) amount FROM `records` r WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 1 AND r.`deleted_at` IS NULL GROUP BY r.`time` having amount>=1;')
        return warrantOrdertakings
    },
    async getStats_resourceOrdertakings(data) {
        const [resourceOrdertakings] = await connection.execute('SELECT r.`time`, count(*) amount FROM `records` r WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`is_mortgage` = 0 AND r.`deleted_at` IS NULL GROUP BY r.`time` having amount>=1;')
        return resourceOrdertakings
    },
    async getStats_pickCars(data) {
        const [pickCars] = await connection.execute('SELECT r.`pick_car_time` time, count( * ) amount FROM `records` r WHERE r.`is_sign` = 1 AND r.`is_pick_car` = 1 ' + data.sql + ' AND r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` HAVING amount >= 1;')
        return pickCars
    },
    async getStats_charges(data) {
        const [charges] = await connection.execute('SELECT r.`charge` FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`charge` != 0 AND r.`deleted_at` IS NULL ORDER BY r.`id`;')
        return charges
    },
    async getStats_chargesSum(data) {
        const [chargesSum] = await connection.execute('SELECT r.`pick_car_time` time, sum(r.`charge`) sum, count(*) amount FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`charge` != 0 AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` having amount>=1 ORDER BY r.`pick_car_time`;')
        return chargesSum
    },
    async getStats_achievements(data) {
        const [achievements] = await connection.execute('SELECT r.`pick_car_time` time, sum(r.`achievement`) sum, count(*) amount FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`charge` != 0 AND r.`achievement` != 0 AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` having amount>=1 ORDER BY r.`pick_car_time`;')
        return achievements
    },
    async getStats_chargesAmount(data) {
        const [charges] = await connection.execute('SELECT r.`pick_car_time` time, COUNT( * ) amount FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '"  AND r.`pick_car_time` <= "' + data.to + '"  AND r.`is_sign` = 1  AND r.`charge` != 0  AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` HAVING amount >= 1 ORDER BY r.`pick_car_time`;')
        return charges
    },
    async getStats_marketCharges(data) {
        const [marketCharges] = await connection.execute('SELECT p.`cost` FROM `packages` p WHERE p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND p.`deleted_at` IS NULL;')
        return marketCharges
    },
    async getStats_notPasss(data) {
        const [notPasss] = await connection.execute('SELECT ANY_VALUE ( r.`time` ) `time`, ANY_VALUE ( rnp.`name` ) `name`, count(*) amount FROM `records` r, `reasons_not_passs` rnp WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`notpass_id` = rnp.`id` AND r.`deleted_at` IS NULL AND rnp.`deleted_at` IS NULL GROUP BY r.`notpass_id` having amount>=1;')
        return notPasss
    },
    async getStats_distributions(data) {
        const [distributions] = await connection.execute('SELECT ANY_VALUE ( r.`time` ) `time`, ANY_VALUE ( d.`name` ) `name`, count(*) amount FROM `records` r, `distributions` d WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`distribution_id` = d.`id` AND r.`deleted_at` IS NULL AND d.`deleted_at` IS NULL GROUP BY r.`distribution_id` having amount>=1;')
        return distributions
    },
    async getStats_checkOuts(data) {
        const [checkOuts] = await connection.execute('SELECT r.`pick_car_time` time, count(*) amount FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" AND r.`charge` != 0 AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` having amount>=1;')
        return checkOuts
    },
    async getStats_trials(data) {
        const [trials] = await connection.execute('SELECT ANY_VALUE ( r.`time` ) `time`, ANY_VALUE ( tc.`name` ) `name`, count(*) amount FROM `records` r, `trial_channels` tc WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`trial_id` = tc.`id` AND r.`deleted_at` IS NULL AND tc.`deleted_at` IS NULL GROUP BY r.`trial_id` having amount>=1;')
        return trials
    },
    async getStats_cancellations(data) {
        const [cancellations] = await connection.execute('SELECT ANY_VALUE ( r.`time` ) `time`, ANY_VALUE ( rc.`name` ) `name`, count(*) amount FROM `records` r, `reasons_cancellations` rc WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`cancellation_id` = rc.`id` AND r.`cancellation_id` != 0 AND r.`deleted_at` IS NULL AND rc.`deleted_at` IS NULL GROUP BY r.`cancellation_id` having amount>=1;')
        return cancellations
    },
    async getStats_pass(data) {
        const [pass] = await connection.execute('SELECT r.`pass_time` time, count( * ) amount FROM `records` r WHERE r.`pass_time` >= "' + data.from + '" AND r.`pass_time` <= "' + data.to + '" AND r.`trial_id` != 0 AND r.`deleted_at` IS NULL GROUP BY r.`pass_time` HAVING amount >= 1;')
        return pass
    },
    async getStats_passSum(data) {
        const [passSum] = await connection.execute('SELECT r.`pass_time` time, sum( r.`loan_amount` ) sum, count( * ) amount FROM `records` r WHERE r.`pass_time` >= "' + data.from + '" AND r.`pass_time` <= "' + data.to + '" AND r.`is_sign` = 1 AND r.`is_ordertaking` = 1 AND r.`loan_amount` != 0 AND r.`deleted_at` IS NULL GROUP BY r.`pass_time` HAVING amount >= 1 ORDER BY r.`pass_time`;')
        return passSum
    },
    async getStats_notSigns(data) {
        const [notSigns] = await connection.execute('SELECT ANY_VALUE ( r.`time` ) `time`, ANY_VALUE ( rns.`name` ) `name`, count(*) amount FROM `records` r, `reasons_not_signings` rns WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" AND r.`notsigning_id` = rns.`id` AND r.`notsigning_id` != 0 AND r.`deleted_at` IS NULL AND rns.`deleted_at` IS NULL GROUP BY r.`notsigning_id` having amount>=1;')
        return notSigns
    },

};


